package com.zcah.db.dbsource.impl;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zcah.db.dbsource.AbstractDbSource;
import com.zcah.db.domain.ColumnProperty;
import com.zcah.db.domain.DbsourceDO;
import com.zcah.db.vo.TableInfo;

public class SqlServerSource extends AbstractDbSource {

	private static final Logger logger = LoggerFactory.getLogger(SqlServerSource.class);

	// 查询所有非系统数据库
	private static final String SQL_SELECT_DBS = "SELECT name FROM master..sysDatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');";

	// 查询当前数据库下所有表schema， 表名， 注释
	private static final String SQL_SELECT_TABLES = "select s.name as sname, a.name AS name, g.VALUE AS comment from sys.schemas s inner join (sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)) on a.SCHEMA_id = s.SCHEMA_id;";

	// 查询指定数据库下指定schema的表的字段结构以及注释
	private static final String SQL_SHOW_COLUMNS = "USE %s;SELECT " + "a.name as column_name, "
			+ "g.[value] as comment,  " + "b.name as column_type  " + "FROM  syscolumns a "
			+ "left join systypes b on a.xtype=b.xusertype  "
			+ "inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' "
			+ "left join syscomments e on a.cdefault=e.id  "
			+ "left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id "
			+ "left join sys.schemas i on d.uid=i.schema_id " + "where b.name is not null and i.name = '%s' "
			+ "and d.name='%s'";

	private static final String URL = "jdbc:sqlserver://%s:%s";

	public SqlServerSource(DbsourceDO db) {
		initSource(db);
		dbUrl = String.format(URL, db.getIp(), db.getPort());
		try {
			connection = DriverManager.getConnection(dbUrl, username, password);
		} catch (SQLException e) {
			errMsg = e.getLocalizedMessage();
		}
	}

	@Override
	public List<TableInfo> getSchemasTables() {
		List<TableInfo> tableInfos = new ArrayList<>();
		ResultSet dbs = null;
		try {
			dbs = getSqlResultSet(SQL_SELECT_DBS);
			List<String> dbNames = new ArrayList<>();
			while (dbs.next()) {
				dbNames.add(dbs.getString("name"));
			}
			dbs.close();
			for (String dbName : dbNames) {
				dbs = getSqlResultSet("use " + dbName + ";" + SQL_SELECT_TABLES);
				while (dbs.next()) {
					TableInfo tableInfo = new TableInfo();
					tableInfo.setTableName(dbName + "." + dbs.getString("sname") + "." + dbs.getString("name"));
					tableInfo.setComment(dbs.getString("comment"));
					tableInfos.add(tableInfo);
				}
			}
			dbs.close();
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		} finally {
			if (dbs != null) {
				try {
					dbs.close();
				} catch (SQLException ignore) {
				}
			}
		}

		return tableInfos;
	}

	@Override
	public List<ColumnProperty> getTableColumns(String fullTableName) {
		String[] names = fullTableName.split("\\.");
		String sql = String.format(SQL_SHOW_COLUMNS, names[0], names[1], names[2]);
		List<ColumnProperty> columnProperties = new ArrayList<>();

		try (ResultSet rs = getSqlResultSet(sql);) {
			while (rs.next()) {
				ColumnProperty columnProperty = new ColumnProperty();
				columnProperty.setColumnComment(rs.getString("comment"));
				columnProperty.setColumnDataType(rs.getString("column_type"));
				columnProperty.setColumnName(rs.getString("column_name"));
				columnProperties.add(columnProperty);
			}
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		}
		return columnProperties;
	}

}
